import win32com.client
import time
from openpyxl import load_workbook
from win32com.client import constants
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill
import shutil
from configparser import ConfigParser


def vlookup(lookup_value, table_array, col_index_num):
    global a
    for row in table_array:
        if row[0].value == lookup_value:
            a = table_array.index(row) - 1
            return row[col_index_num - 1].value


t = time.localtime()
date_today = time.strftime("%Y.%m.%d", t)

path = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/"
lj_qlxxb = path + date_today + "/source_file_Ⅱ/欠料信息表" + date_today + ".xlsx"
# region win32-excel处理部分
excel = win32com.client.gencache.EnsureDispatch('Excel.application')
# excel = win32com.client.DispatchEx('Excel.application')

excel.Visible = False
excel.DisplayAlerts = False
e_wb_qlxxb = excel.Workbooks.Open(lj_qlxxb)
e_ws_qlxxb_y = e_wb_qlxxb.Worksheets(1)
e_ws_qlxxb_y.Name = "欠料信息表" + date_today

SrcData = e_ws_qlxxb_y.Range("A1:M" + str(e_ws_qlxxb_y.UsedRange.Rows.Count))
e_ws_qlxxb_ts = e_wb_qlxxb.Worksheets.Add()
e_ws_qlxxb_ts.Name = "数据透视表"
StartPvt = e_ws_qlxxb_ts.Range("A1")
pt_cache = e_wb_qlxxb.PivotCaches().Create(SourceType=constants.xlDatabase, SourceData=SrcData)
pt = pt_cache.CreatePivotTable(TableDestination=StartPvt, TableName="PivotTable1")
pt.AddFields(RowFields=["材料品号"])
pt.AddDataField(Field=pt.PivotFields("欠料数量"))
for i in range(1, pt.RowFields.Count + 1):
    field = pt.PivotFields(i)
    subtotal_tuple = field.Subtotals
    subtotal_list = list(subtotal_tuple)
    for k in range(len(subtotal_tuple)):
        subtotal_list[k] = False
    field.Subtotals = subtotal_list
pt.RowAxisLayout(constants.xlTabularRow)

e_ws_qlxxb_wldr = e_wb_qlxxb.Worksheets('Sheet2')
e_ws_qlxxb_wldr.Name = "物料信息表导入列"

e_wb_qlxxb.Worksheets("Sheet3").Delete()

e_wb_qlxxb.SaveAs(path + date_today + "/欠料信息表" + date_today + ".xlsx")
e_wb_qlxxb.Application.Quit()
excel.Application.Quit()
# endregion

lj_qlxxb = path + date_today + "/欠料信息表" + date_today + ".xlsx"
wb_qlxxb = load_workbook(lj_qlxxb)
ws_qlxxb_y = wb_qlxxb["欠料信息表" + date_today]

lj_gdwcqk_xlsx = path + date_today + "/source_file_Ⅱ/工单完工情况报表" + date_today + ".xlsx"
wb_gdwcqk = load_workbook(lj_gdwcqk_xlsx)
ws_gdwcqk = wb_gdwcqk.active

ws_gdwcqk.insert_cols(3)
for i in range(2, ws_gdwcqk.max_row + 1):
    ws_gdwcqk.cell(i, 3).value = ws_gdwcqk.cell(i, 1).value + "-" + ws_gdwcqk.cell(i, 2).value

table_array = list(ws_gdwcqk['C1':'H' + str(ws_gdwcqk.max_row)])
for i in range(2, ws_qlxxb_y.max_row + 1):
    global a
    a = 0
    if len(ws_qlxxb_y.cell(i, 8).value) != 0 and ws_qlxxb_y.cell(i, 8).value[0] == "5":
        if vlookup(ws_qlxxb_y.cell(i, 8).value, table_array, 2) is not None:
            if vlookup(ws_qlxxb_y.cell(i, 8).value, table_array, 2)[0] == "3":
                ws_qlxxb_y.cell(i, 14).value = vlookup(ws_qlxxb_y.cell(i, 8).value, table_array, 2)
                ws_qlxxb_y.cell(i, 15).value = vlookup(ws_qlxxb_y.cell(i, 8).value, table_array, 3)
                ws_qlxxb_y.cell(i, 16).value = vlookup(ws_qlxxb_y.cell(i, 8).value, table_array, 4)
                ws_qlxxb_y.cell(i, 17).value = vlookup(ws_qlxxb_y.cell(i, 8).value, table_array, 6)
            else:
                while table_array[a][1].value[0] != '3':
                    a -= 1
                ws_qlxxb_y.cell(i, 14).value = table_array[a][1].value
                ws_qlxxb_y.cell(i, 15).value = table_array[a][2].value
                ws_qlxxb_y.cell(i, 16).value = table_array[a][3].value
                ws_qlxxb_y.cell(i, 17).value = table_array[a][5].value
# 格式调整
list1 = [13, 12, 11, 9, 7, 6, 5]
for i in list1:
    ws_qlxxb_y.delete_cols(i)
list1 = [15, 30, 40, 10, 20, 10, 15, 28, 28, 10]
for i in range(1, ws_qlxxb_y.max_column + 1):
    ws_qlxxb_y.column_dimensions[get_column_letter(i)].width = list1[i - 1]
ws_qlxxb_y["G1"] = "品号"
ws_qlxxb_y["H1"] = "型号"
ws_qlxxb_y["I1"] = "规格"
ws_qlxxb_y["J1"] = "数量"

fill = PatternFill(patternType="solid", fgColor="F4B382")  # 单元格填充颜色
ws_qlxxb_y["A1"].fill = fill
ws_qlxxb_y["B1"].fill = fill
ws_qlxxb_y["C1"].fill = fill
ws_qlxxb_y["D1"].fill = fill
ws_qlxxb_y["E1"].fill = fill
ws_qlxxb_y["F1"].fill = fill
fill = PatternFill(patternType="solid", fgColor="91AADF")  # 单元格填充颜色
ws_qlxxb_y["G1"].fill = fill
ws_qlxxb_y["H1"].fill = fill
ws_qlxxb_y["I1"].fill = fill
ws_qlxxb_y["J1"].fill = fill

ws_qlxxb_y.auto_filter.ref = "A1:I" + str(ws_qlxxb_y.max_row)
ws_qlxxb_y.freeze_panes = 'A2'

#  处理 物料信息表导入列
ws_qlxxb_ts = wb_qlxxb["数据透视表"]
ws_qlxxb_wldr = wb_qlxxb["物料信息表导入列"]

for i in range(2, ws_qlxxb_ts.max_row + 1):
    ws_qlxxb_wldr.cell(i, 1).value = ws_qlxxb_ts.cell(i, 1).value
    ws_qlxxb_wldr.cell(i, 2).value = ws_qlxxb_ts.cell(i, 2).value

for i in range(ws_qlxxb_wldr.max_row, 1, -1):
    if ws_qlxxb_wldr.cell(i, 1).value is None:
        ws_qlxxb_wldr.delete_rows(i)
    elif ws_qlxxb_wldr.cell(i, 1).value[0] != "1":
        ws_qlxxb_wldr.delete_rows(i)

del wb_qlxxb["数据透视表"]

ws_qlxxb_wldr["A1"].value = "品号"
ws_qlxxb_wldr["B1"].value = "欠料数量"
ws_qlxxb_wldr.column_dimensions["A"].width = 15
ws_qlxxb_wldr.column_dimensions["B"].width = 10

wb_qlxxb.save(path + date_today + "/欠料信息表" + date_today + ".xlsx")

lj_config = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/Tools/PMCtools_Python_v2.0/dist/config.ini"
cfg = ConfigParser()
cfg.read(lj_config)
if not cfg.get('set', 'Test mode'):
    shutil.copy(path + date_today + "/欠料信息表" + date_today + ".xlsx",
                "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具")

print(date_today+":qlxxb succeed!")
